<?php

namespace App\Console\Commands;

use App\Http\Services\Oss;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use PHPExcel;
use PHPExcel_IOFactory;

class CommandCreateNewYearRoomExcel extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'CreateNewYearUserRoomExcel';

    protected $description = '生成房间总榜';

    protected $successLog = 'CreateNewYearExcel'; //日志
    protected $errorLog = 'error_CreateNewYearExcel'; //错误日志

    protected $apiCon = 'api'; //数据库
    protected $activityConn = 'activity';

    protected $newYearStream = 'new_year_2024_stream';//年度送礼表
    protected $user = 'user';
    protected $union_user = 'union_user';
    protected $union = 'union';
    protected $room = 'room';
    protected $union_room = 'union_room';
    protected $jd_stream_new_year = 'new_year_2024118_jd_log';
    protected $excelOutTable = 'new_year_excel_2024';

    protected $activityStart = '1706090400';//2024-01-24 18:00:00
    protected $activityEnd = '1706724000';//2024-02-01 02:00:00

    protected $oss;
    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->oss = new Oss();
        parent::__construct();
        
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        do{
            if(time() < $this->activityStart || time() > $this->activityEnd){
                write_log($this->successLog,'活动结束',[]);
                break;
            }
            //查询准备要生成的房间总榜数据
            $needDatasCount = DB::connection($this->activityConn)->table($this->newYearStream)
            ->count();
            if($needDatasCount == 0){
                write_log($this->successLog,'无数据',[]);
                break;
            }
            $roomRes = DB::connection($this->activityConn)->table($this->newYearStream)
            ->select(DB::raw('SUM(gift_total) as total,room_id'))
            ->groupBy('room_id')
            ->get();
            $roomRes = objToArray($roomRes);
            $roomIds = array_column($roomRes,'room_id');
            //获取房间信息
            $roomInfos = DB::connection($this->apiCon)->table($this->room)->whereIn('room_id',$roomIds)->get();
            $roomInfos = objToArray($roomInfos);
            $roomInfos = array_column($roomInfos,null,'room_id');
            $excelData = [];
            foreach($roomRes as $k=>$v){
                //公会信息
                $roomUnionInfo = DB::connection($this->apiCon)->table($this->union_room)->where('room_id',$v['room_id'])->first();
                $roomUnionInfo = objToArray($roomUnionInfo);
                $unionInfo = [];
                if(!empty($roomUnionInfo)){
                    //公会信息
                    $unionInfo =  DB::connection($this->apiCon)->table($this->union)->where('id',$roomUnionInfo['un_id'])->first();
                    $unionInfo = objToArray($unionInfo);
                    //获取公会长信息
                    $unionUserAdminInfo = DB::connection($this->apiCon)->table($this->user)->where('user_id',$unionInfo['un_user_id'])->first();
                    $unionUserAdminInfo = objToArray($unionUserAdminInfo);
                }
                $rank = $k+1;
                //礼物1总
                $giftOne = DB::connection($this->activityConn)->table($this->newYearStream)
                ->select(DB::raw('SUM(gift_total) as total'))
                ->where('is_ticket',0)
                ->where('room_id',$v['room_id'])
                ->where('gift_id',206)
                ->get();
                $giftOne = objToArray($giftOne);
                //礼物2总
                $giftTwo = DB::connection($this->activityConn)->table($this->newYearStream)
                ->select(DB::raw('SUM(gift_total) as total'))
                ->where('is_ticket',0)
                ->where('room_id',$v['room_id'])
                ->where('gift_id',215)
                ->get();
                $giftTwo = objToArray($giftTwo);
                //礼物3总
                $giftThree = DB::connection($this->activityConn)->table($this->newYearStream)
                ->select(DB::raw('SUM(gift_total) as total'))
                ->where('is_ticket',0)
                ->where('room_id',$v['room_id'])
                ->where('gift_id',223)
                ->get();
                $giftThree = objToArray($giftThree);
                //礼物4总
                $giftFour = DB::connection($this->activityConn)->table($this->newYearStream)
                ->select(DB::raw('SUM(gift_total) as total'))
                ->where('is_ticket',0)
                ->where('room_id',$v['room_id'])
                ->where('gift_id',224)
                ->get();
                $giftFour = objToArray($giftFour);
                //礼物5总
                $giftFive = DB::connection($this->activityConn)->table($this->newYearStream)
                ->select(DB::raw('SUM(gift_total) as total'))
                ->where('is_ticket',0)
                ->where('room_id',$v['room_id'])
                ->where('gift_id',225)
                ->get();
                
                $giftFive = objToArray($giftFive);
                //人气票总数
                $ticket = DB::connection($this->activityConn)->table($this->newYearStream)->where('gift_id',226)->where('room_id',$v['room_id'])->sum('gift_total');
                $excelData[$k] = [
                    'rank' => $rank,//排名
                    'room_id' => $v['room_id'],//房间id
                    'name' => empty($roomInfos[$v['room_id']]['name']) ? '音洛聊天室' : $roomInfos[$v['room_id']]['name'],//房间名称
                    'union_name' => isset($unionInfo['name']) ? $unionInfo['name'] : '无',//公会名称
                    'union_id' => isset($unionInfo['id']) ? $unionInfo['id'] : '0',//公会id
                    'union_user_id' => isset($unionInfo['un_user_id']) ? $unionInfo['un_user_id'] : '0',//公会长id
                    'union_user_name' => isset($unionUserAdminInfo['nickname']) ? $unionUserAdminInfo['nickname'] : '',//公会长名称
                    'points' => $v['total'],//房间积分
                    'gift_one' => empty($giftOne[0]['total']) ? 0 : $giftOne[0]['total'],//礼物1送出总
                    'gift_two' => empty($giftTwo[0]['total']) ? 0 : $giftTwo[0]['total'],//礼物2送出总
                    'gift_three' => empty($giftThree[0]['total']) ? 0 : $giftThree[0]['total'],//礼物3送出总
                    'gift_four' => empty($giftFour[0]['total']) ? 0 : $giftFour[0]['total'],//礼物4送出总
                    'gift_five' => empty($giftFive[0]['total']) ? 0 : $giftFive[0]['total'],//礼物5送出总
                    'ticket' => $ticket,//人气送出总
                ]; 
            }
            //生成Excel
            $fileres = $this->CreateExcel($excelData,'room.xlsx',1);
            if($fileres){
                //上传oss
                $url = $this->upLoadOss($fileres['fileName']);
                if($url){
                    //删除文件
                    if (file_exists(storage_path('room.xlsx'))) {
                        unlink(storage_path('room.xlsx'));     //删除
                    }
                    
                    $insert = [
                        'type' => 5,
                        'excel_url' => $url,
                        'created_at' => Carbon::now()->toDateTimeString()
                    ];
                    DB::beginTransaction();
                    try{
                        $insertGetId = DB::connection($this->activityConn)->table($this->excelOutTable)->insertGetId($insert);
                        DB::commit();
                        return true;
                        write_log($this->successLog,'INFO 数据生成成功',$insertGetId);
                    }catch(\Exception $e){
                        DB::rollBack();
                        write_log($this->errorLog,'ERROR 数据错误',['msg'=>$e->getMessage(),'data'=>$insert]);
                        return false;
                    }
                }
            }else{
                write_log($this->errorLog,'ERROR 数据Excel失败',$excelData);  
            }


        }while(false);
        
    }

    //excel 生成
    public function CreateExcel($csvData, $fileName='data.excel', $type=1){
        //生成EXCEL
        $objPHPExcel = new PHPExcel();
        try {
            $objSheet = $objPHPExcel->getActiveSheet();
            if($type == 1){
                $this->excelTemp($objSheet, $objPHPExcel, $csvData);
            }
                
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $filePath = storage_path($fileName);
            $objWriter->save($filePath);
            
            return ['fileName' => $fileName, 'filePath' => $filePath];
        } catch (\PHPExcel_Exception $e) {
            return false;
            write_log($this->successLog,'生成Excel数据失败',[$e]);
        }
    }

    //上传OSS
    public function upLoadOss($fileName=''){
        if(empty($fileName)){
            write_log($this->successLog,'文件不存在',[]);
            return false;
        }
        $upload_config =  curl_request(config('kkaudio.kkaudio_api') . "/inner/system/put_file_url", ['type' => 24, 'exts' => 'xlsx', 'noase' => 1], $contentType = 'urlencoded', $httpMethod = 'GET');

        $uri = $upload_config['data'][0]['uri'];
        $formatUri = ltrim($uri, '/');
        $localFilePath = storage_path($fileName);
        $ossRes = $this->oss->uploadFile($formatUri, $localFilePath);
        if (!$ossRes) {
           write_log($this->errorLog, '上传文件过程中发生错误',$ossRes);
           return false;
        }
        $url = '/'.$formatUri;
        return $url;
    }

    //房间日榜
    public function excelTemp($objSheet, $objPHPExcel, $csvData){
        //工作表标题
        $objSheet->setTitle("房间榜数据");
        //第一行内容，放列标题
        $objSheet->setCellValue("A1", "排名");
        $objSheet->setCellValue("B1", "房间ID");
        $objSheet->setCellValue("C1", "房间昵称");
        $objSheet->setCellValue("D1", "归属公会");
        $objSheet->setCellValue("E1", "归属公会ID");
        $objSheet->setCellValue("F1", "公会长id");
        $objSheet->setCellValue("G1", "公会长昵称");
        $objSheet->setCellValue("H1", "总积分");
        $objSheet->setCellValue("I1", "礼物1积分");
        $objSheet->setCellValue("J1", "礼物2积分");
        $objSheet->setCellValue("K1", "礼物3积分");
        $objSheet->setCellValue("L1", "礼物4积分");
        $objSheet->setCellValue("M1", "礼物5积分");
        $objSheet->setCellValue("N1", "人气票");
        //设置第一行的背景颜色
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:N1')
            ->getFill()
            ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB('FFFF00');
        //后面行循环放内容
        for($i=0;$i<count($csvData);$i++){
            $lineNum = $i+2;
            $objSheet->setCellValue("A".$lineNum, $csvData[$i]['rank']);
            $objSheet->setCellValue("B".$lineNum, $csvData[$i]['room_id']);
            $objSheet->setCellValue("C".$lineNum, $csvData[$i]['name']);
            $objSheet->setCellValue("D".$lineNum, $csvData[$i]['union_name']);
            $objSheet->setCellValue("E".$lineNum, $csvData[$i]['union_id']);
            $objSheet->setCellValue("F".$lineNum, $csvData[$i]['union_user_id']);
            $objSheet->setCellValue("G".$lineNum, $csvData[$i]['union_user_name']);
            $objSheet->setCellValue("H".$lineNum, $csvData[$i]['points']);
            $objSheet->setCellValue("I".$lineNum, $csvData[$i]['gift_one']);
            $objSheet->setCellValue("J".$lineNum, $csvData[$i]['gift_two']);
            $objSheet->setCellValue("K".$lineNum, $csvData[$i]['gift_three']);
            $objSheet->setCellValue("L".$lineNum, $csvData[$i]['gift_four']);
            $objSheet->setCellValue("M".$lineNum, $csvData[$i]['gift_five']);
            $objSheet->setCellValue("N".$lineNum, $csvData[$i]['ticket']);
        }
    }
}